﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Common;
using Dto;

namespace SqlServerDAL
{
    public class UsersDAL
    {
        private const string Waming = "SqlServerDAL.UsersDAL";

        public bool Save(UsersDto dto)
        {
            try
            {
                string sql;
                var param = new List<SqlParameter>
                                {
                                    new SqlParameter("@UserName", SqlDbType.VarChar) {Value = dto.UserName},
                                    new SqlParameter("@UserPwd", SqlDbType.VarChar) {Value = dto.UserPwd},
                                    new SqlParameter("@UserEmail", SqlDbType.VarChar) {Value = dto.UserEmail},
                                    new SqlParameter("@UserTime", SqlDbType.DateTime) {Value = dto.UserTime},
                                    new SqlParameter("@UserType", SqlDbType.Int) {Value = dto.UserType}
                                };
                if (dto.UserId == 0)
                {
                    sql =
                        @"insert into Users(UserName,UserPwd,UserEmail,UserTime,UserType) values(@UserName,@UserPwd,@UserEmail,
                        @UserTime,@UserType)";
                }
                else
                {
                    sql =
                        @"update Users set UserName=@UserName,UserPwd=@UserPwd,UserEmail=@UserEmail,UserTime=@UserTime,
                        UserType=@UserType where UserId=@UserId";
                    param.Add(new SqlParameter("@UserId", SqlDbType.Int) { Value = dto.UserId });
                }
                SqlHelper.ExecuteNonQuery(SqlHelper.connStr, CommandType.Text, sql, param.ToArray());
                return true;
            }
            catch (Exception ex)
            {
                Log.LogHandler.LogError(Waming + ".Save(UsersDto dto)", ex);
                return false;
            }
        }

        public Hashtable FindOne(string email, string userName, string pwd)
        {
            try
            {
                Hashtable ht = null;
                var parameters = new List<SqlParameter>();
                const string sql = "select top 1 * from Users";
                var whereSql = "";
                if (!string.IsNullOrEmpty(email))
                {
                    whereSql += " where UserEmail=@UserEmail";
                    parameters.Add(new SqlParameter("@UserEmail", SqlDbType.VarChar) { Value = email });
                }
                if (!string.IsNullOrEmpty(userName))
                {
                    whereSql += whereSql == "" ? " where UserName=@UserName" : " and UserName=@UserName";
                    parameters.Add(new SqlParameter("@UserName", SqlDbType.VarChar) { Value = userName });
                }
                if (!string.IsNullOrEmpty(pwd))
                {
                    whereSql += whereSql == "" ? " where UserPwd=@UserPwd" : " and UserPwd=@UserPwd";
                    parameters.Add(new SqlParameter("@UserPwd", SqlDbType.VarChar) { Value = pwd });
                }
                using (var red = SqlHelper.ExecuteReader(SqlHelper.connStr, CommandType.Text, sql + whereSql, parameters.ToArray()))
                {
                    if (red.Read())
                    {
                        ht = SqlHelper.FillHashtable(red);
                    }
                }
                return ht;
            }
            catch (Exception ex)
            {
                Log.LogHandler.LogError(Waming + ".FindOne(string email)", ex);
                return null;
            }
        }

        public Hashtable FindById(int id)
        {
            try
            {
                Hashtable ht = null;
                const string sql = "select * from Users where UserId=@UserId";
                var param = new SqlParameter("@UserId", SqlDbType.Int) { Value = id };
                using (var red = SqlHelper.ExecuteReader(SqlHelper.connStr, CommandType.Text, sql, param))
                {
                    if (red.Read())
                    {
                        ht = SqlHelper.FillHashtable(red);
                    }
                }
                return ht;
            }
            catch (Exception ex)
            {
                Log.LogHandler.LogError(Waming + ".FindById(int id)", ex);
                return null;
            }
        }
    }
}
